/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Factory.DAO.MySQL;

import Factory.DAOFactory;
import Factory.DAO.UserDAO;
import Factory.Tables.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import DatabaseConnection.DBConnectionFactory;
import java.util.ArrayList;

/**
 *
 * @author Patrick
 */
public class MySQLUserDAO extends UserDAO{

    
    
    @Override
    public User authenticate(String username,String password) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from user natural join userdef where username = ? and password = ? and isactive=1");
            pstmt.setString(1, username);
            pstmt.setString(2, encryptPassword(password));
            ResultSet rs = pstmt.executeQuery();
            User myUser = null;
            while (rs.next()){
                myUser = new User();
                myUser.setUserID(rs.getInt("iduser"));
                myUser.setUserPic(rs.getString("userpic"));
                myUser.setUsername(rs.getString("username"));
                myUser.setPassword(decryptPassword(rs.getString("password")));
                myUser.setFirstName(rs.getString("fname"));
                myUser.setLastName(rs.getString("lname"));
                myUser.setAge(rs.getInt("age"));
                myUser.setGender(rs.getString("gender"));
                myUser.setAddress(rs.getString("address"));
                myUser.setBirthday(rs.getDate("birthday"));
                myUser.setEmail(rs.getString("email"));
                myUser.setUserDefID(rs.getInt("iduserdef"));
                myUser.setUserDefName(rs.getString("userdefname"));
                myUser.setUserActive(1);
            }
            conn.close();
            return myUser;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    @Override
    public User adminAuthenticate(String username,String password) {
        
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from user natural join userdef where username = ? and password = ? and isactive=1 and iduserdef = 1");
            pstmt.setString(1, username);
            pstmt.setString(2, encryptPassword(password));
            ResultSet rs = pstmt.executeQuery();
            User myUser = null;
            while (rs.next()){
                myUser = new User();
                myUser.setUserID(rs.getInt("iduser"));
                myUser.setUserPic(rs.getString("userpic"));
                myUser.setUsername(rs.getString("username"));
                myUser.setPassword(decryptPassword(rs.getString("password")));
                myUser.setFirstName(rs.getString("fname"));
                myUser.setLastName(rs.getString("lname"));
                myUser.setAge(rs.getInt("age"));
                myUser.setGender(rs.getString("gender"));
                myUser.setAddress(rs.getString("address"));
                myUser.setBirthday(rs.getDate("birthday"));
                myUser.setEmail(rs.getString("email"));
                myUser.setUserDefID(rs.getInt("iduserdef"));
                myUser.setUserDefName(rs.getString("userdefname"));
                myUser.setUserActive(1);
            }
            conn.close();
            return myUser;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
           
        }
        return null;
    }
    

    @Override
    public void addUser(User newUser) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("insert into user(userpic,username,password,fname,lname,age,gender,address,birthday,email,iduserdef,isactive,reviewed,rated,datejoined,timejoined) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            pstmt.setString(1, newUser.getUserPic());
            pstmt.setString(2, newUser.getUsername());
            pstmt.setString(3, encryptPassword(newUser.getPassword()));
            pstmt.setString(4, newUser.getFirstName());
            pstmt.setString(5, newUser.getLastName());
            pstmt.setInt(6, newUser.getAge());
            pstmt.setString(7, newUser.getGender());
            pstmt.setString(8, newUser.getAddress());
            pstmt.setDate(9, newUser.getBirthday());
            pstmt.setString(10, newUser.getEmail());
            pstmt.setInt(11, newUser.getUserDefID());
            pstmt.setInt(12, 1);
            pstmt.setInt(13, 0);
            pstmt.setInt(14, 0);
            pstmt.setDate(15, newUser.getCreatedDate());
            pstmt.setTime(16, newUser.getCreatedTime());
            
            
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public boolean isUsernameUnique(String username) {
        boolean usernameUnique = true;
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select username from user");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                if(rs.getString("username").equals(username)){
                    usernameUnique = false;
                    break;
                }
            }
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return usernameUnique;
    }

    @Override
    public boolean isEmailAddressUnique(String email) {
        boolean emailUnique = true;
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select email from user");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                if(rs.getString("email").equals(email)){
                    emailUnique = false;
                    break;
                }
            }
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return emailUnique;
    }

    @Override
    public void updateUser(User obj) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update user SET userpic=?, username=?, fname=?, lname=?, age=?, gender=?, address=?, birthday =?, email=? where iduser=?");
            pstmt.setString(1, obj.getUserPic());
            pstmt.setString(2, obj.getUsername());
            pstmt.setString(3, obj.getFirstName());
            pstmt.setString(4, obj.getLastName());
            pstmt.setInt(5, obj.getAge());
            pstmt.setString(6, obj.getGender());
            pstmt.setString(7, obj.getAddress());
            pstmt.setDate(8, obj.getBirthday());
            pstmt.setString(9, obj.getEmail());
            pstmt.setInt(10, obj.getUserID());
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public User getUser(int userID) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from user where iduser=?");
            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();
            User myUser = null;
            while(rs.next()){
                myUser = new User();
                myUser.setUserID(rs.getInt("iduser"));
                myUser.setUserPic(rs.getString("userpic"));
                myUser.setUsername(rs.getString("username"));
                myUser.setPassword(decryptPassword(rs.getString("password")));
                myUser.setFirstName(rs.getString("fname"));
                myUser.setLastName(rs.getString("lname"));
                myUser.setAge(rs.getInt("age"));
                myUser.setGender(rs.getString("gender"));
                myUser.setAddress(rs.getString("address"));
                myUser.setBirthday(rs.getDate("birthday"));
                myUser.setEmail(rs.getString("email"));
                myUser.setUserDefID(rs.getInt("iduserdef"));
                myUser.setUserActive(rs.getInt("userActive"));
            }
            conn.close();
            return myUser;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public boolean isEmailAddressUnique(int userID, String email) {
        boolean emailUnique = true;
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select iduser,email from user");
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                if(rs.getInt("iduser")!=userID && rs.getString("email").equals(email)){
                    emailUnique = false;
                    break;
                }
            }
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return emailUnique;
    }

    @Override
    public void changePassword(int userID, String password) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update user SET password=? where iduser=?");
            pstmt.setString(1, super.encryptPassword(password));
            pstmt.setInt(2, userID);
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLUserDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<User> getAllUsers() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from user order by fame, lname");
            ResultSet rs = pstmt.executeQuery();
            ArrayList<User> result = new ArrayList<>();
            User myUser;
            while(rs.next()){
                myUser = new User();
                myUser.setUserID(rs.getInt("iduser"));
                myUser.setUserPic(rs.getString("userpic"));
                myUser.setUsername(rs.getString("username"));
                myUser.setPassword(decryptPassword(rs.getString("password")));
                myUser.setFirstName(rs.getString("fname"));
                myUser.setLastName(rs.getString("lname"));
                myUser.setAge(rs.getInt("age"));
                myUser.setGender(rs.getString("gender"));
                myUser.setAddress(rs.getString("address"));
                myUser.setBirthday(rs.getDate("birthday"));
                myUser.setEmail(rs.getString("email"));
                myUser.setUserDefID(rs.getInt("iduserdef"));
                myUser.setUserActive(rs.getInt("isactive"));
                result.add(myUser);
            }
            conn.close();
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLInventoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    
    
}
